import json
from typing import List, Any

import pandas as pd
import requests
from sympy import false
# 定义请求地址
url = 'http://10.32.41.25:40816/api/chat/query/parse'
url2 = 'http://10.32.41.25:40816/api/chat/query/execute'

# 读取 Excel 文件、结果写入新的 excel 文件根据环境修改文件路径
inputfile_path = 'kb_ques.xlsx'
success_outputfile_path = 'success_output_kb.xlsx'
error_outputfile_path = 'error_output_kb.xlsx'


# 读取 Excel 文件，包括 '问话' 和 'token' 列
df = pd.read_excel(inputfile_path, usecols=['问句', 'Token'], sheet_name="Sheet1")

# 从 Excel 中获取 token（假设使用第一行的 token 值）
if 'Token' in df.columns and not df.empty:
    token_value = df.at[0, 'Token']  # 取第一行（index 0）的 token
    if isinstance(token_value, str) and token_value.strip():
        headers = {
            'Content-Type': 'application/json;charset=UTF-8',
            'Authorization': f'Bearer {token_value.strip()}'
        }
    else:
        raise ValueError("Excel 文件中的 token 值为空或无效，请检查 'token' 列。")
else:
    raise ValueError("Excel 文件中未找到 'token' 列或表格为空，请检查输入文件是否包含 'token' 列。")



# 初始化一个空的 DataFrame 来存储响应数据
success_response_df = []
error_df = []

# 遍历 DataFrame 的每一行（从第二行开始，即索引为 1 的行）
for index, row in df.iterrows():
    try:
        # 生成请求报文
        request_data = {
            "queryText": row['问句'],
            "chatId": 123,  # 根据环境修改
            "agentId": 3  # 根据环境修改
        }
        print(f"Sending request for row {index}: {request_data}")

        # 发送 POST 请求
        response = requests.post(url, json=request_data, headers=headers)

        # 检查响应状态码并处理响应数据
        if response.status_code == 200:
            response_json = response.json()
            print(f"Response for row {index}: {response.text}")
            querydata = response_json.get('data')
            if querydata:
                queryText = querydata.get('queryText', '')
                queryId = querydata.get('queryId', '')
            else:
                print(f"No valid 'data' in response for row {index}.")
                error_df.append({
                    'row': str(index),
                    'error_message': f"Error in first request for row {index}: No valid 'data' in response."
                })
                continue
        else:
            print(f"Error in first request for row {index}: Status code {response.status_code}, data: {request_data}")
            error_df.append({
                'row': str(index),
                'error_message': f"Error in first request for row {index}: Status code {response.status_code}, data: {request_data}"
            })
            continue

        # 生成第二个接口的请求报文
        request_data2 = {
            "queryText": queryText,
            "queryId": queryId,
            "chatId": 123,  # 根据环境修改
            "parseId": 1
        }
        print(f"Sending second request for row {index}: {request_data2}")

        responseexcute = requests.post(url2, json=request_data2, headers=headers)

        if responseexcute.status_code == 200:
            response_content = responseexcute.text
            if response_content:
                response_json2 = responseexcute.json()
                if response_json2 is not None:
                    data = response_json2.get('data')
                    if data is not None:
                        chat_context = data.get('chatContext')
                        if chat_context is not None and 'sqlInfo' in chat_context:
                            querydata2 = response_json2['data']
                            querysql = response_json2["data"]["chatContext"]["sqlInfo"]
                            print(f"Successful response for row {index}: {responseexcute.text}")
                            # 将响应数据添加到 success_response_df 中
                            success_response_df.append({
                                'row': str(index),
                                'queryText': queryText,
                                's2SQL': querysql.get('s2SQL', ''),
                                'correctS2SQL': querysql.get('correctS2SQL', ''),
                                'querySql': querydata2.get('querySql', ''),
                                'queryResults': querydata2.get('queryResults', '')
                            })
                        else:
                            print(f"No 'chatContext' or 'sqlInfo' in response for row {index}.")
                            error_df.append({
                                'row': str(index),
                                'error_message': f"No 'chatContext' or 'sqlInfo' in response for row {index}."
                            })
                    else:
                        print(f"No 'data' in response for row {index}.")
                        error_df.append({
                            'row': str(index),
                            'error_message': f"No 'data' in response for row {index}."
                        })
                else:
                    print(f"Response is not valid JSON or is None for row {index}.")
                    error_df.append({
                        'row': str(index),
                        'error_message': f"Response is not valid JSON or is None for row {index}."
                    })
            else:
                print(f"Empty response content for row {index}.")
                error_df.append({
                    'row': str(index),
                    'error_message': f"Empty response content for row {index}."
                })
        else:
            print(f"Error in second request for row {index}: Status code {responseexcute.status_code}, data: {request_data2}")
            error_df.append({
                'row': str(index),
                'error_message': f"Error in second request for row {index}: Status code {responseexcute.status_code}, data: {request_data2}"
            })
    except Exception as e:
        print(f"An unexpected error occurred for row {index}: {str(e)}")
        error_df.append({
            'row': str(index),
            'error_message': f"Unexpected error for row {index}: {str(e)}"
        })

# 将列表转换为 DataFrame
success_response_dt = pd.DataFrame(success_response_df)
error_dt = pd.DataFrame(error_df)

# 将行号（doc_id）作为 response_df 的索引，以便与原始数据行对齐
if success_response_dt.empty:
    success_response_dt.index = pd.RangeIndex(start=0, stop=0)
else:
    success_response_dt.index += 1
if error_dt.empty:
    error_dt.index = pd.RangeIndex(start=0, stop=0)
else:
    error_dt.index += 1

# 将成功的响应数据写入一个 Excel 文件
success_response_dt.to_excel(success_outputfile_path, sheet_name='Sheet', index=False)

# 将错误数据写入另一个 Excel 文件
error_dt.to_excel(error_outputfile_path, sheet_name='Sheet', index=False)

print("数据已更新并保存到 Excel 文件中。")